
*****************************************************************************************************************
* File: Clean Survey Data																				*
* Project: Crisis Management: Personal Financial Well-Being and Public Attitudes Toward Government Intervention *
* Contact: Tyler Girard					  																		*
* Email: tgirard@purdue.edu																						*
*****************************************************************************************************************


************************************************************

****					START HERE						***

************************************************************

* Set working drive. Original data located in file: "1 Original Data"
*use "COVID Survey Raw Data.dta", clear

*Gen CERB Count

gen cerb_status=.
replace cerb_status =1 if CERB==1 & Q25_1==1
replace cerb_status =.5 if CERB==1 & Q25_1==.
replace cerb_status=0 if CERB==2
replace cerb_status=0 if CERB==. & Q25_1==.
replace cerb_status =1 if CERB==. & Q25_1==1

*recode three people who answered yes to Q25_1 but no to CERB (but then answered the CERB questions in the affirmatve)
replace cerb_status =1 if cerb_==0 & Q25_1==1

label define cerb_status 0 "Not CERB"  1 "CERB"
label values cerb_status cerb_status

tab Q5 if cerb_status==.5, nol
replace cerb_status=0 if (cerb_status==.5 & Q5==1)
replace cerb_status=0 if (cerb_status==.5 & Q5==4)
replace cerb_status=0 if (cerb_status==.5 & Q5==6)
replace cerb_status=0 if (cerb_status==.5 & Q5==7)
replace cerb_status=0 if (cerb_status==.5 & Q5==10)
replace cerb_status=0 if (cerb_status==.5 & Q5==11)
replace cerb_status=0 if (cerb_status==.5 & Q5==12)
label var cerb_status "CERB Status"

tab cerb_status, mi

tab1 Q1 Q2 Q3, mi

sort cerb_
tab  cerb_ Q3 if cerb_s!=.5, col chi

gen cerb_revised=cerb_status
recode cerb_revised (.5=1)


label var CERB "Receiving CERB"
label var Q2 "Gender"
label var Q3 "Region"
label var Q4 "Postal Code"
label var Q5 "Employment Status"
label var Q5_12 "Employment Status Text"
label var Q6 "Income"
label var Q7 "Education"
label var Q8 "Visible Minority"
label var Q9 "Marital Status"
label var Q10 "Ppl under 18 yrs"
label var Q11 "Living Situation"
label var Q11_6_TEXT "Living Situation Text"
label var Q12 "Mortgage"
label var Q13 "Left-Right"
drop Q13_NPS_GROUP


label var Q14_1 "Financial News"
label var Q14_2 "Financial Podcast"
label var Q14_3 "Financial Website"
label var Q14_4 "Financial Newsletter"
label var Q14_5 "Financial Advisor"
label var Q14_6 "Other Media" 
label var Q14_6_ "Other Media Text" 

label var Q15 "Responsible for Bills"
label var Q16 "HH Budget"
label var Q17 "Stay within Budget"
label var Q18 "Many ppl struggling financially due to COVID"
label var Q19_1 "Credit part of lifestyle"
label var Q19_2 "Most people in too much debt"
label var Q20 "Pre-COVID debt"
label var Q21 "New consumer debt"
label var Q22 "Months of expenses"
label var Q23 "Feeling financial hardship"
label var Q24 "Feeling financial hardship similar to me"

label var Q25_1 CERB
label var Q25_2 "Mortgage Deferral"
label var Q25_3 "Car Payment Deferral"
label var Q25_4 "Credit Card Deferral"
label var Q25_5 "Financial Relief from Companies"
label var Q25_6 "Support from family/friend"
label var Q25_7 "New Credit Card"
label var Q25_8 "New Line of Credit"
label var Q25_9 "New HELOC"
label var Q25_10 "New Personal Loan"
label var Q25_11 "Increased CC Limit"
label var Q25_12 "Increased LoC Limit"
label var Q25_13 "Increased HELOC Limit"
label var Q25_14 "Withdrawn Savings"
label var Q25_15 "Withdrawn Retirement Savings"
label var Q25_16 "Withdrawn Investments"
label var Q25_17 "Withdrawn Other Funds"
label var Q25_18 "Missed Payments"

label var Q26 "Family members on CERB"
label var Q27 "Family eligible for CERB"
label var Q28 "Why apply for CERB"

label var Q29_1 "Mortgage"
label var Q29_2 "Rent"
label var Q29_3 "Medication"
label var Q29_4 "Food"
label var Q29_5 "Entertainment"
label var Q29_6 "Clothing"
label var Q29_7 "Caring for Family"
label var Q29_8 "Paying Debt"
label var Q29_9 "Renovations"
label var Q29_10 "Landscaping"
label var Q29_11 "Vehicle Purchase"
label var Q29_12 "Emergency Fund"
label var Q29_13 "Increase Personal Savings"
label var Q29_14 "Personal Investments"
label var Q29_15 "Loan Money to Family or Friend"
label var Q29_16 "New Home"
label var Q29_17 "Other"

label var Q30 "With CERB, increase spending on non-essentials"
label var Q31 "With CERB, increase spending on entertinment"
label var Q32 "With CERB, increase spending on clothing"
label var Q33_1 "Required to return to my job before end of CERB"
label var Q33_2 "I will delay returning to work for as long as possible"
label var Q33_3 "I feel anxious thinking about my financial situation after CERB ends" 
label var Q33_4 "Making more money per month than normal with CERB"
label var Q33_5 "Saving a portion of CERB to pay the taxes"
label var Q34_1 "Credit Counseling "
label var Q34_2 "Bankruptcy Protection" 
label var Q34_3 "Free online financial education course" 
label var Q34_4 "Pay online financial education course"
label var Q34_5 "DIY Budgeting"
label var Q34_6 "Consult bank"
label var Q34_7 "Consult accountant"
label var Q34_8 "Not sure"
label var Q35_1 "Cheerful"
label var Q35_2 "Calm and relaxed"
label var Q35_3 "Active and energetic"
label var Q35_4 "Fresh and rested"
label var Q35_5 "Life filled with things that interest"
label var Q36 "Stress owing to COVID"
label var Q37 "Feel like I have no control"
label var Q38 "Fully transparent about fin situation"

label var Q39_1 "People would Disapprove of Financial Situation"
label var Q39_2 "Worry What Others Think"
label var Q39_3 "Negative Attitude Toward Me"
label var Q39_4 "Treat Me Differently"
label var Q39_5 "Ppl Think Not Capable of Managing Money"

label var Q40 "Federal Handling of Crisis"
label var Q41 "Provincial Handling of Crisis"
label var Q42_1 "Feds Should Push Banks Toward Greater Relief Measures"
label var Q42_2 "Feds Should Allow RRSP Withdraws"
label var Q42_3 "Prov Should Give More Money to Students"
label var Q42_4 "Concerned About Debt"
label var Q43 "Basic Income Guarantee"

drop Q11_6_TEXT___Parent_Topics Q11_6_TEXT___Topics

*Addressing missing values

*Where missing data was a product of apparent random error, we recoded to mean/median/mode

/* Q1 Age - None
Q2 Gender - None
Q3 Region - 1 --> Recoded to mode (ON)
Q4 Postal - N/A
Q5 Employment - 2 --> Recoded to other (12)
Q6 HH Income - 4 --> recoded to median (3)
Q7 Education - 5 --> recoded to median (4)
Q8 VM - 9 --> recoded to prefer not to respond (5)
Q9 Marital Status - 6 --> recoded to mode (1)
Q10 # Persons in HH --> recoded to none (0)
Q11 Living Situation - 7 --> recoded to mode (1)
Q12 Mortgage - leave missing (question logic)
Q13 Left/Right Placement - 7 --> recoded to mean (5.34)
Q14_1 Financial News - 7 --> recoded to median (3)
Q14_2 Financial Podcast - 9 --> recoded to median (5)
Q14_3 Financial Website - 7 --> recoded to median (4)
Q14_4 Financial Newsletter - 10 --> recoded to median (4)
Q14_5 Financial Advisor - 288 --> recoded to median (4) (added this later)
Q14_6 Other - 290 --> recoded to median (5)
Q15 Financial Responsibility - 7  --> recoded to median (1)
Q16 HH Budget - 9 --> recoded to mode (1)
Q17 Stay within Budget - 545 --> leave missing (question logic)
Q18 Ppl are Financially Strggling due to COVID - 8 --> recoded to median (6)
Q19_1 Credit as Lifestyle - 8 --> recoded to median (5)
Q19_2 Most ppl too much debt - 8 --> recoded to median (6)
Q20 Consumer debt - 10 - 8 --> recoded to median (2)
Q21 New consumer debt - 14 --> recoded to median (1)
Q22 Months of savings - 12 --> recoded to median (3)
Q23 Financial hardship - 16 --> recoded to median (2)
Q24 Ppl struggling financially - 18 --> recoded to median (4)
Q25_1 - Q25_18 - recode all CERB to 0; leave non-CERB as missing
Q26 # in HH Accessed CERB - 28 --> recoded to (0)
Q27 CERB Eligible - 32 --> recoded to (0)
Q28 Why apply for CERB - 610 --> leave missing (question logic)
Q29_1 - Q29_17 How are you using CERB - recode all CERB to 0; leave non-CERB as missing
Q30 With CERB, increase spending on non-essentials - 611 --> recode all CERB to median (3.5); leave non-CERB as missing
Q31 With CERB, increase spending on entertinment - 611 - recode all CERB to median (3); leave non-CERB as missing
Q32 With CERB, increase spending on clothing - 614 - recode all CERB to median (3); leave non-CERB as missing
Q33_1 required to return to my job before the CERB program was finished - 616 - recode all CERB to median (4); leave non-CERB as missing
Q33_2 I will delay returning to work for as long as possible - 617 - recode all CERB to median (2); leave non-CERB as missing
Q33_3 I feel anxious thinking about my financial situation after CERB ends - 615 - recode all CERB to median (5); leave non-CERB as missing
Q33_4 making more money per month than normal with CERB - 615 - recode all CERB to median (4); leave non-CERB as missing
Q33_5 saving a portion of CERB to pay the taxes - 615 - recode all CERB to median (5); leave non-CERB as missing
Q34_1 Credit Counseling - 46 --> recode to median (2)
Q34_2 Bankruptcy Protection - 42 --> recode to median (1)
Q34_3 free online financial education course - 42 --> recode to median (3)
Q34_4 Pay online financial education course- 42 --> recode to median (2)
Q34_5 DIY Budgeting Apps - 43 --> recode to median (4)
Q34_6 Consult bank - 43 --> recode to median (3)
Q34_7 Consult accountant - 42 --> recode to median (3)
Q35_1 Cheerful - 74 --> recode to median (3)
Q35_2 Calm and relaxed - 74 --> recode to median (3)
Q35_3 Active and energetic - 74 --> recode to median (2)
Q35_4 Fresh and rested - 76 --> recode to median (2)
Q35_5 Life filled with things that interest - 75 --> recode to median (3)
Q36 Level of Stress - 75 --> recode to median (5)
Q37 No control - 77 --> recode to median (5)
Q38 Financial Transparency with partner - (logic) - 601 --> (do not recode)
Q39_1 People would Disapprove of Financial Situation - 87 --> recode to median (4)
Q39_2 Worry What Others Think - 87 --> recode to median (3)
Q39_3 Negative Attitude Toward Me - 88 --> recode to median (3)
Q39_4 Treat Me Differently - 88 --> recode to median (3)
Q39_5 Ppl Think Not Capable of Managing Money - 89 --> recode to median (4)
Q40 Federal Handling of Crisis - 91 --> recode to median (4)
Q41 Provincial Handling of Crisis - 91 --> recode to median (4)
Q42_1 Feds Should Push Banks Toward Greater Relief Measures - 96 --> recode to median (5)
Q42_2 Feds Should Allow RRSP Withdraws - 96 --> recode to median (5)
Q42_3 Prov Should Give More Money to Students - 98 --> recode to median (5)
Q42_4 Concerned About Debt - 97 --> recode to median (5)
Q43 Basic Income Guarantee - 98 --> recode to median (6)
*/
clonevar Q3_na = Q3
recode Q3 (.=3)

clonevar Q5_na = Q5
recode Q5 (.=12)

clonevar Q6_na = Q6
recode Q6 (.=3) (7=3)

clonevar Q7_na = Q7
recode Q7 (.=4)

clonevar Q8_na = Q8
recode Q8 (.=5)

clonevar Q9_na = Q9
recode Q9 (.=1)

clonevar Q10_na = Q10
recode Q10 (.=0)

clonevar Q11_na = Q11
recode Q11 (.=1)

clonevar Q13_na = Q13
recode Q13 (.=5)

clonevar Q14_1_na = Q14_1
recode Q14_1 (.=3)

clonevar Q14_2_na = Q14_2
recode Q14_2 (.=5)

clonevar Q14_3_na = Q14_3
recode Q14_3 (.=4)

clonevar Q14_4_na = Q14_4
recode Q14_4 (.=4)

clonevar Q14_5_na = Q14_5
recode Q14_5 (.=4)

clonevar Q14_6_na = Q14_6
recode Q14_6 (.=5)

clonevar Q15_na = Q15
recode Q15 (.=1)

clonevar Q16_na = Q17
recode Q16 (.=1)

clonevar Q18_na = Q18
recode Q18 (.=6)

clonevar Q19_1_na = Q19_1
recode Q19_1 (.=5)

clonevar Q19_2_na = Q19_2
recode Q19_2 (.=6)

clonevar Q20_na = Q20
recode Q20 (.=2) (10=2)

clonevar Q21_na = Q21
recode Q21 (.=1)

clonevar Q22_na = Q22
recode Q22 (.=3)

clonevar Q23_na = Q23
recode Q23 (.=2)

clonevar Q24_na = Q24
recode Q24 (.=4)

* no need to clone variable Q25_1
recode Q25_1 (.=1) if cerb_s==1
foreach i in 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18{
recode Q25_`i' (.=0) if cerb_s==1
}

clonevar Q26_na = Q26
recode Q26 (.=0)

clonevar Q27_na = Q27
recode Q27 (.=0)

* no need to clone variable Q29
foreach i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17{
recode Q29_`i' (.=0) if cerb_s==1
}

clonevar Q30_na = Q30
recode Q30 (.=3) if cerb_s==1

clonevar Q31_na = Q31
recode Q31 (.=3) if cerb_s==1

clonevar Q32_na = Q32
recode Q32 (.=3) if cerb_s==1

clonevar Q33_1_na = Q33_1
recode Q33_1 (.=4) if cerb_s==1

clonevar Q33_2_na = Q33_2
recode Q33_2 (.=2) if cerb_s==1

clonevar Q33_3_na = Q33_3
recode Q33_3 (.=5) if cerb_s==1

clonevar Q33_4_na = Q33_4
recode Q33_4 (.=4) if cerb_s==1

clonevar Q33_5_na = Q33_5
recode Q33_5 (.=5) if cerb_s==1

clonevar Q34_1_na = Q34_1
recode Q34_1 (.=2)

clonevar Q34_2_na = Q34_2
recode Q34_2 (.=1)

clonevar Q34_3_na = Q34_3
recode Q34_3 (.=3)

clonevar Q34_4_na = Q34_4
recode Q34_4 (.=2)

clonevar Q34_5_na = Q34_5
recode Q34_5 (.=4)

clonevar Q34_6_na = Q34_6
recode Q34_6 (.=3)

clonevar Q34_7_na = Q34_7
recode Q34_7 (.=3)

clonevar Q35_1_na = Q35_1
recode Q35_1 (.=3)

clonevar Q35_2_na = Q35_2
recode Q35_2 (.=3)

clonevar Q35_3_na = Q35_3
recode Q35_3 (.=2)

clonevar Q35_4_na = Q35_4
recode Q35_4 (.=2)

clonevar Q35_5_na = Q35_5
recode Q35_5 (.=3)

clonevar Q36_na = Q36
recode Q36 (.=5)

clonevar Q37_na = Q37
recode Q37 (.=5)

clonevar Q39_1_na = Q39_1
recode Q39_1 (.=4)

clonevar Q39_2_na = Q39_2
recode Q39_2 (.=3)

clonevar Q39_3_na = Q39_3
recode Q39_3 (.=3)

clonevar Q39_4_na = Q39_4
recode Q39_4 (.=3)

clonevar Q39_5_na = Q39_5
recode Q39_5 (.=4)

*Mean of oth handling indicators is 4 so setting missing there

gen fed_handling=Q40
clonevar fed_handling_na = fed_handling

recode fed_handling (.=4) (0=4)
recode fed_handling_na (0=.)


label var fed_handling "Federal Financial Handling"
label var fed_handling_na "Federal Financial Handling NA"


gen prov_handling=Q41
clonevar prov_handling_na = prov_handling

recode prov_handling (.=4) (0=4)
recode prov_handling_na (0=.)

label var prov_handling "Provincial Financial Handling"
label var prov_handling_na "Provincial Financial Handling NA"

label define good 1 "Very bad" 2 "Bad" 3 "Neither" 4 "Good" 5 "Very good", replace
label values fed_handling prov_handling good
label values fed_handling_na prov_handling_na good

tab fed_handling Q40
tab prov_handling Q41

*Mean of all indicators is 5 so setting missing there

gen bank_relief=Q42_1
clonevar bank_relief_na=bank_relief
recode bank_relief (.=5)
label var bank_relief "Banks should provide greater relief"
label var bank_relief_na "Banks should provide greater relief NA"

gen rrsp_withdraw=Q42_2 
clonevar rrsp_withdraw_na=rrsp_withdraw
recode rrsp_withdraw (.=5)
label var rrsp_withdraw "Feds should allow RRSP withdrawals"
label var rrsp_withdraw_na "Feds should allow RRSP withdrawals NA"

gen students_money=Q42_3
clonevar students_money_na=students_money
recode students_money (.=5)
label var students_money "Government should provide funds for students"
label var students_money_na "Government should provide funds for students NA"

gen debt_accrual=Q42_4
clonevar debt_accrual_na=debt_accrual
recode debt_accrual (.=5)
label var debt_accrual "Concern over accumulation of debt"
label var debt_accrual_na "Concern over accumulation of debt NA"

label define agree 1 "Strongly disagree" 2 "Disagree" 3 "Somewhat disagree" 4 "Neither" 5 "Somewhat agree" 6 "Agree" 7 "Strongly agree", replace
label values bank_relief bank_relief_na rrsp_withdraw rrsp_withdraw_na students_money students_money_na debt_accrual debt_accrual_na agree

recode Q43 (2=1) (3=2) (4=3) (5=4) 
label values Q43 agree
tab Q43
gen big=Q43
clonevar big_na=big

***Fixing coding because we forgot "agree"
recode big (1=1) (2/3=2) (4=3) (6=4) (7=5) (.=4)
recode big_na (1=1) (2/3=2) (4=3) (6=4) (7=5)
label define agree5 1 "Strongly disagree" 2 "Disagree" 3 "Neither" 4 "Agree" 5 "Strongly agree", replace
label values big big_na agree5
label var big "Recoded Basic Income Guarantee"
label var big_na "Recoded Basic Income Guarantee NA"

gen female=1 if Q2==2
replace female=0 if female!=1
tab female Q2
label var female "Female"

gen unemployed=1 if Q5==2 | Q5==3 | Q5==5 | Q5== 7 | Q5==8 | Q5== 10 | Q5==11
replace unemployed=0 if unemployed!=1
label var unemployed "Unemployed"

gen unemployed_na=1 if Q5_na==2 | Q5_na==3 | Q5_na==5 | Q5_na== 7 | Q5_na==8 | Q5_na== 10 | Q5_na==11
replace unemployed_na=0 if unemployed_na!=1
replace unemployed_na=. if missing(Q5_na)
label var unemployed_na "Unemployed NA"

gen income=Q6
gen income_na=Q6_na
label var income "Income"
label var income_na "Income NA"

gen education=Q7
gen education_na=Q7_na
label var education "Education"
label var education "Education NA"

gen children=1 if Q10!=0
replace children=0 if Q10==0
clonevar children_na=children
replace children_na=. if missing(Q10_na)
label var children "Children"
label var children_na "Children NA"

gen mortgage=Q12
label var mortgage "Mortgage"

gen leftright=Q13
gen leftright_na=Q13_na
label var leftright "Left-Right Self-Placement"
label var leftright_na "Left-Right Self-Placement NA"

gen vis_minority=Q8
recode vis_minority (1=1) (else=0)
*clonevar vis_minority_na = vis_minority
*recode vis_minority_na=. if missing(Q8_na)
*label var vis_minority "Visible Minority"

gen finnewsconsumer=Q14_1+Q14_2+Q14_3+Q14_4+Q14_5
replace finnewsconsumer=(finnewsconsumer/25)*10
label var finnewsconsumer "Financial news consumption"

gen womenhiincome=1 if female==1 & inrange(Q6,5,6)
replace womenhiincome=0 if womenhiincome!=1
label var womenhiincome "Women x High Income"

gen atlantic=0
replace atlantic=1 if Q3==1
label var atlantic "Atlantic"

gen ontario=0
replace ontario=1 if Q3==3
label var ontario "Ontario"

gen quebec=0
replace quebec=1 if Q3==2
label var quebec "Quebec"

gen alberta=0
replace alberta=1 if Q3==5
label var alberta "Alberta"

gen bc=0
replace bc=1 if Q3==6
label var bc "BC"

gen prairies=0
replace prairies=1 if Q3==4
label var prairies "Prairies"

drop if Q3==7

clonevar ppl_struggling_7=Q18
clonevar fin_hardship=Q23
clonevar hardship_similar= Q24
clonevar new_debt=Q21
recode new_debt (10=1)

clonevar ppl_struggling_7_na=Q18_na
clonevar fin_hardship_na=Q23_na
clonevar hardship_similar_na= Q24_na
clonevar new_debt_na=Q21_na
recode new_debt_na (10=.)

gen ppl_struggling =ppl_struggling_7 
recode ppl_struggling (1=1) (2/3=2) (4=3) (5/6=4) (7=5)
tab ppl_struggling_7 ppl_struggling

gen ppl_struggling_na =ppl_struggling_7_na
recode ppl_struggling_na (1=1) (2/3=2) (4=3) (5/6=4) (7=5)
tab ppl_struggling_7_na ppl_struggling_na

label var ppl_struggling_7 "Many people are struggling financially"
label var ppl_struggling "Many people are struggling financially"
label var fin_hardship "I am experiencing financial hardship"
label var hardship_similar "Ppl experiencing fin hardship similar to me"
label var new_debt "New debt accumulation since COVID"

label var ppl_struggling_7_na "Many people are struggling financially NA"
label var ppl_struggling_na "Many people are struggling financially NA"
label var fin_hardship_na "I am experiencing financial hardship NA"
label var hardship_similar_na "Ppl experiencing fin hardship similar to me NA"
label var new_debt_na "New debt accumulation since COVID NA"


*alpha .94
gen stigmatization=Q39_1 +Q39_2+ Q39_3+ Q39_4+ Q39_5
replace stigmatization=(stigmatization/35)*10
label var stigmatization "Financial stigmatization"

alpha bank_relief rrsp_withdraw students_money
*scale reliability is .70

gen govt_intervention=bank_relief+rrsp_withdraw+students_money
replace govt_int=(govt_intervention/21)*10
tab govt_ 
label var govt_intervention "Government Intervention Index"

drop cerb_status
rename cerb_revised cerb
label var cerb "CERB recipient"
drop ppl_struggling_7

order fed_handling prov_handling govt_intervention big bank_relief rrsp_withdraw students_money debt_accrual ppl_struggling fin_hardship hardship_similar female cerb unemployed income education children mortgage leftright vis_minority finnewsconsumer new_debt stigmatization womenhiincome atlantic quebec ontario prairies alberta bc, after (Q43)

drop if Q3_na == .

* save data to folder: "2 Clean Data"
*save "COVID Survey Clean Data.dta", replace

**************************************
*			SAMPLE CHECK			*
**************************************

* Income
sum income if inrange(income,1,8), det
* Median = 3 ($60-$90)

* Education
sum education if inrange(education,1,11), det
* Median = 4 (Completed CEGEP, college or university)

* Lef-Right
sum education if inrange(education,1,11), det
* Mean = 5.3 (SE = 0.6)

**************************************
*			END OF FILE  			*
**************************************
